package cj.web.vote.dao;
/*
 *  
 *  
*/
import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import cj.web.admin.domain.MemuInfo;
import cj.web.vote.domain.Song;
import cj.web.vote.domain.Vote;
import cj.web.vote.domain.VoteSongRef;

/**
 * 
 * @author 
 *
 */
@DAO
public interface VoteDAO{
	
	@SQL("SELECT * FROM Vote WHERE deleteFlag=1 #if(:t.name!=''){ AND name LIKE '%##(:t.name)%' } ORDER BY updateAT DESC LIMIT :t.start,:t.length ")
    List<Vote> query(@SQLParam("t") Vote vote);
	
	@SQL("SELECT COUNT(*) FROM Vote WHERE deleteFlag=1 #if(:t.name!=''){ AND name LIKE '%##(:t.name)%' } ")
    int querysize(@SQLParam("t") Vote vote);
    
    @SQL("SELECT * FROM Vote WHERE id=:1")
    Vote queryById(int id);
    
    @SQL("SELECT * FROM Vote WHERE startDate < date_format(:1,'%Y-%m-%d %H:%i:%s') and endDate > date_format(:1,'%Y-%m-%d %H:%i:%s') order by createAT desc limit 1")
    Vote queryNowVote(String date);
    
    @SQL("SELECT B.song_id,B.song_name,B.song_url,B.song_img,B.singer_name,B.singer_university,B.voteSongId ,(COUNT(A.wxOpenid)+B.votePlus) AS vote_num from VoteLog A RIGHT OUTER JOIN (SELECT A.id AS song_id,A.name AS song_name,A.songURL AS song_url,A.imgURL AS song_img,A.singer AS singer_name,A.university AS singer_university,B.id AS voteSongId,A.votePlus FROM Song A ,(SELECT	*	FROM	VoteSongRef WHERE voteId = :1 AND type = :2 LIMIT :3) B  WHERE A.id = B.songId)B ON A.voteSongID = B.voteSongId GROUP BY B.voteSongId ORDER BY vote_num DESC,B.song_id ASC")
    List<Map> querySongListFromVote(String voteId,String type,int limit);
    
    @SQL("INSERT INTO Vote (name,description,startDate,endDate,deleteFlag,createAT,updateAT) VALUES (:t.name,:t.description,:t.startDate,:t.endDate,1,now(),now()) ")
    void create(@SQLParam("t") Vote vote);
	
	@SQL("UPDATE Vote SET name=:t.name,description=:t.description,startDate=:t.startDate,endDate=:t.endDate,updateAT=now() WHERE id=:t.id ")
    void update(@SQLParam("t") Vote vote);
    
    @SQL("UPDATE Vote SET deleteFlag=0 WHERE id=:1 ")
    void deleteById(int id);
    
    //------------------------
    @SQL("SELECT songId FROM VoteSongRef r WHERE r.voteId = :1")
	List<String> getSongIdsByVoteId(int voteId);
    
    @SQL("SELECT * FROM Song WHERE deleteFlag=1")
	List<Song> getAll();
    
    @SQL("DELETE FROM VoteSongRef WHERE voteId = :1")
	void deleteByVoteId(int voteId);
    
    @SQL("INSERT INTO VoteSongRef (voteId,songId,type,createAT,updateAT) VALUES (:t.voteId,:t.songId,:t.type,now(),now())")
    void createVoteSongRef(@SQLParam("t") VoteSongRef voteSongRef);
}